CREATE TABLE [dbo].[tb_Funciones]
(
[idFuncion] [int] NOT NULL IDENTITY(1, 1),
[Descripcion] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Mnemo] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Horas] [int] NULL,
[Puntos] [int] NULL,
[Sistema] [int] NOT NULL,
[RegimenLaboral] [int] NOT NULL,
[Financiamiento] [int] NOT NULL,
[Presupuesto] [int] NULL,
[Finalidad] [int] NOT NULL,
[TipoDeFuncion] [int] NOT NULL,
[RegimenSalarial] [int] NOT NULL,
[RegimenSuplencias] [int] NOT NULL,
[RegimenDesigna] [int] NOT NULL,
[Temporalidad] [int] NOT NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[deletetb_FuncionesLUI] ON [dbo].[tb_Funciones]
 FOR DELETE AS 
SET NOCOUNT ON

		 --DELETE tb_PaquetesPlazas FROM tb_PaquetesPlazas
		 --WHERE Funcion = (SELECT idFuncion FROM deleted)
		 DELETE tb_Plazas FROM tb_Plazas
		 WHERE Funcion in (SELECT idFuncion FROM deleted)
		 DELETE tb_AperturasDeReestructura FROM tb_AperturasDeReestructura
		 WHERE Funcion in (SELECT idFuncion FROM deleted)

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[updatetb_FuncionesLUI] ON [dbo].[tb_Funciones]
 FOR UPDATE AS 
SET NOCOUNT ON
 IF (update(idFuncion)) begin 
	 SELECT * FROM inserted 
	 IF @@ROWCOUNT = 1 begin 
		 UPDATE tb_PaquetesPlazas
		 SET Funcion = (SELECT idFuncion FROM inserted)
		 WHERE tb_PaquetesPlazas.Funcion = (SELECT idFuncion FROM deleted)
		 UPDATE tb_Plazas
		 SET Funcion = (SELECT idFuncion FROM inserted)
		 WHERE tb_Plazas.Funcion = (SELECT idFuncion FROM deleted)
		 UPDATE tb_AperturasDeReestructura
		 SET Funcion = (SELECT idFuncion FROM inserted)
		 WHERE tb_AperturasDeReestructura.Funcion = (SELECT idFuncion FROM deleted)
	end  ELSE begin 
		 ROLLBACK TRANSACTION 
 end 
 end
GO
ALTER TABLE [dbo].[tb_Funciones] ADD CONSTRAINT [PK_tb_FuncionesLUI] PRIMARY KEY NONCLUSTERED  ([idFuncion]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_Mnemo] ON [dbo].[tb_Funciones] ([Mnemo]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_FuncionesLUI_tb_Finalidades] FOREIGN KEY ([Finalidad]) REFERENCES [dbo].[tb_Finalidades] ([idFinalidad])
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tbFuncionesLUI_tbFinalidades] FOREIGN KEY ([Finalidad]) REFERENCES [dbo].[tb_Finalidades] ([idFinalidad])
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_FuncionesLUI_tb_FuentesDeFinanciamiento] FOREIGN KEY ([Financiamiento]) REFERENCES [dbo].[tb_FuentesDeFinanciamiento] ([idFuenteFinanciamiento])
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tbFuncionesLUI_tbFuentesDeFinanciamiento] FOREIGN KEY ([Financiamiento]) REFERENCES [dbo].[tb_FuentesDeFinanciamiento] ([idFuenteFinanciamiento])
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_FuncionesLUI_tb_PartidasPresupuestarias] FOREIGN KEY ([Presupuesto]) REFERENCES [dbo].[tb_PartidasPresupuestarias] ([idPartida])
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tbFuncionesLUI_tbPartidasPresupuestarias] FOREIGN KEY ([Presupuesto]) REFERENCES [dbo].[tb_PartidasPresupuestarias] ([idPartida])
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_FuncionesLUI_tb_RegimenDesignaciones] FOREIGN KEY ([RegimenDesigna]) REFERENCES [dbo].[tb_RegimenDesignaciones] ([idRegimenDesignacion])
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tbFuncionesLUI_tbRegimenDesignaciones] FOREIGN KEY ([RegimenDesigna]) REFERENCES [dbo].[tb_RegimenDesignaciones] ([idRegimenDesignacion])
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_FuncionesLUI_tb_RegimenLaboral] FOREIGN KEY ([RegimenLaboral]) REFERENCES [dbo].[tb_RegimenLaboral] ([idRegimenLaboral])
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tbFuncionesLUI_tbRegimenLaboral] FOREIGN KEY ([RegimenLaboral]) REFERENCES [dbo].[tb_RegimenLaboral] ([idRegimenLaboral])
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_FuncionesLUI_tb_RegimenDeSuplencias] FOREIGN KEY ([RegimenSuplencias]) REFERENCES [dbo].[tb_RegimenDeSuplencias] ([idRegimenSuplencia])
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tbFuncionesLUI_tbRegimenDeSuplencias] FOREIGN KEY ([RegimenSuplencias]) REFERENCES [dbo].[tb_RegimenDeSuplencias] ([idRegimenSuplencia])
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_FuncionesLUI_tb_Sistemas] FOREIGN KEY ([Sistema]) REFERENCES [dbo].[tb_Sistemas] ([idSistema])
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tbFuncionesLUI_tbSistemas] FOREIGN KEY ([Sistema]) REFERENCES [dbo].[tb_Sistemas] ([idSistema])
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_FuncionesLUI_tb_Temporalidad] FOREIGN KEY ([Temporalidad]) REFERENCES [dbo].[tb_Temporalidad] ([idTemporalidad])
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tbFuncionesLUI_tbTemporalidad] FOREIGN KEY ([Temporalidad]) REFERENCES [dbo].[tb_Temporalidad] ([idTemporalidad])
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_FuncionesLUI_tb_TiposDeFuncion] FOREIGN KEY ([TipoDeFuncion]) REFERENCES [dbo].[tb_TiposDeFuncion] ([idTipoFuncion])
GO
ALTER TABLE [dbo].[tb_Funciones] WITH NOCHECK ADD CONSTRAINT [FK_tbFuncionesLUI_tbTiposDeFuncion] FOREIGN KEY ([TipoDeFuncion]) REFERENCES [dbo].[tb_TiposDeFuncion] ([idTipoFuncion])
GO
